﻿CREATE TRIGGER yaf_Group_update
ON dbo.yaf_Group
FOR UPDATE
AS
    BEGIN
        DECLARE  @BoardID INT
        DECLARE  @GroupID INT
        DECLARE  @Flags INT
        DECLARE inserted_cursor CURSOR  FOR
        SELECT BoardID,
               GroupID,
               Flags
        FROM   inserted
        OPEN inserted_cursor
        FETCH NEXT FROM inserted_cursor
        INTO @BoardID,
             @GroupID,
             @Flags
        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF (@Flags & 2) <> 0
            BEGIN
                -- This is the guest group. Check for other guest groups
                IF EXISTS (SELECT 1
                           FROM   dbo.yaf_Group
                           WHERE  BoardID = @BoardID
                           AND GroupID <> @GroupID
                           AND (Flags & 2) <> 0)
                BEGIN
                    RAISERROR ('There are already other groups marked as guest groups',16,1)
                END
            END
            ELSE
            BEGIN
                -- This is not the guest group. Check for other guest groups
                IF NOT EXISTS (SELECT 1
                               FROM   dbo.yaf_Group
                               WHERE  BoardID = @BoardID
                               AND (Flags & 2) <> 0)
                BEGIN
                    RAISERROR ('There are no other groups marked as guest groups',16,1)
                END
            END
            FETCH NEXT FROM inserted_cursor
            INTO @BoardID,
                 @GroupID,
                 @Flags
        END
        CLOSE inserted_cursor
        DEALLOCATE inserted_cursor
    END


